using System;
using System.Text.RegularExpressions;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using Microsoft.ApplicationBlocks.Data;

////////////////////////////////////////////////////////////////////////////////////////////////////
// namespace: Dimok
//
// summary:	.
////////////////////////////////////////////////////////////////////////////////////////////////////

namespace Dimok{

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Summary description for XmlEx. </summary>
	///
	/// <remarks>	Dimon, 11/23/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public class XmlEx{

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Default constructor. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public XmlEx(){}
		#region GetLevels ============

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Clean levels xml. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="xml">			The xml. </param>
		/// <param name="tagName">	Name of the tag. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string CleanLevelsXML(string xml,string tagName){
			Regex re = new Regex("(</?"+tagName+")_\\d+");
			return re.Replace(xml,"$1");
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a sort. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="orderBy">	Amount to order by. </param>
		///
		/// <returns>	The sort. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static string GetSort(string orderBy){
			return Regex.Match(orderBy,"ORDER BY (.+)",RegexOptions.IgnoreCase).Groups[1].Value;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the levels xml. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="connStr">			The connection string. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="sqlParams">		Options for controlling the sql. </param>
		///
		/// <returns>	The levels xml. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetLevelsXml(string connStr,string sql,string parentField,string childField,string prefix,string levelName,params SqlParameter[] sqlParams){
			return GetLevelsXml(connStr,sql,parentField,childField,prefix,levelName,"",sqlParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the levels xml. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="connStr">			The connection string. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="orderBy">			Amount to order by. </param>
		/// <param name="sqlParams">		Options for controlling the sql. </param>
		///
		/// <returns>	The levels xml. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetLevelsXml(string connStr, string sql, string parentField, string childField, string prefix, string levelName, string orderBy, params SqlParameter[] sqlParams) {
			DataSet ds = GetLevels(connStr,sql,parentField,childField,prefix,levelName,orderBy,sqlParams);
			string ret = CleanLevelsXML(ds.GetXml(),levelName);
			return prefix != "" ? ret.Replace(levelName,prefix+":"+levelName) : ret;
		}
		//TODO:Improve speed by using view instead of pulling two queries

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the levels. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="connStr">			The connection string. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="sqlParams">		Options for controlling the sql. </param>
		///
		/// <returns>	The levels. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet GetLevels(string connStr, string sql, string parentField, string childField, string prefix, string levelName, params SqlParameter[] sqlParams) {
			return GetLevels(connStr,sql,parentField,childField,prefix,levelName,"",sqlParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the levels. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="connStr">			The connection string. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="orderBy">			Amount to order by. </param>
		/// <param name="sqlParams">		Options for controlling the sql. </param>
		///
		/// <returns>	The levels. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet GetLevels(string connStr, string sql, string parentField, string childField, string prefix, string levelName, string orderBy, params SqlParameter[] sqlParams) {
			try {
				DataSet dsTop = SqlHelper.ExecuteDataset(connStr, CommandType.Text, sql + " " + orderBy, sqlParams);
				if (dsTop.Tables[0].Rows.Count == 0) return dsTop;
				GetSubLevel(null, dsTop.Tables[0], parentField, childField, GetSort(orderBy), prefix, levelName);
				dsTop.Tables.RemoveAt(0);
				SetColumnsMapping(dsTop, MappingType.Attribute);
				return dsTop;
			} catch (Exception exc) {
				exc.Data["SQL"] = sql;
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a sub level. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="dtParent">			The dt parent. </param>
		/// <param name="dtChild">			The dt child. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="sort">					The sort. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void GetSubLevel(DataTable dtParent, DataTable dtChild, string parentField, string childField, string sort, string prefix, string levelName) {
			GetSubLevel(dtParent,dtChild,parentField,childField,sort,prefix,levelName,0);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a sub level. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="dtParent">			The dt parent. </param>
		/// <param name="dtChild">			The dt child. </param>
		/// <param name="parentField">	The parent field. </param>
		/// <param name="childField">		The child field. </param>
		/// <param name="sort">					The sort. </param>
		/// <param name="prefix">				The prefix. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="level">				The level. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void GetSubLevel(DataTable dtParent,DataTable dtChild,string parentField,string childField,string sort,string prefix,string levelName,int level){
			string inFilter = dtParent == null ? "0" : GetINFilter(dtParent.Rows,parentField);
			if( inFilter == "" )return;
			DataTable dt = GetChildTable(inFilter,dtChild,childField,levelName+"_"+level,sort);
			if( dt.Rows.Count == 0 )return;
			if( prefix.Length > 0 )dt.Prefix = prefix;
			dtChild.DataSet.Tables.Add(dt);
			if( dtParent != null ){
				DataRelation dr = new DataRelation("REL_"+level,dtParent.Columns[parentField],dt.Columns[childField],true);
				dr.Nested = true;
				dtParent.DataSet.Relations.Add(dr);
			}
			GetSubLevel(dt,dtChild,parentField,childField,sort,prefix,levelName,level+1);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a child table. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="inFilter">			The in filter. </param>
		/// <param name="dt">						The dt. </param>
		/// <param name="filterField">	The filter field. </param>
		/// <param name="levelName">		Name of the level. </param>
		/// <param name="sort">					The sort. </param>
		///
		/// <returns>	The child table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataTable GetChildTable(string inFilter,DataTable dt,string filterField,string levelName,string sort){
			DataTable dtOut = dt.Clone();
			DataView dv = dt.DefaultView;
			dv.RowFilter = filterField + " IN(" + inFilter + ")";
			if( sort != "" )dv.Sort = sort;
			foreach( DataRowView row in dv){
				dtOut.Rows.Add(row.Row.ItemArray);
			}
			dtOut.TableName = levelName;
			return dtOut;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets an in filter. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="rows">			The rows. </param>
		/// <param name="inField">	The in field. </param>
		///
		/// <returns>	The in filter. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static string GetINFilter(DataRowCollection rows,string inField){
			ArrayList al = new ArrayList();
			foreach( DataRow row in rows )
				al.Add(row[inField].ToString());
			return al.Count > 0 ? string.Join(",",(string[])al.ToArray(typeof(System.String))) : "";
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		/// <param name="mt">	The mt. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SetColumnsMapping( DataSet ds,MappingType mt ){
			foreach( DataTable dt in ds.Tables )
				SetColumnsMapping(dt,mt);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="dt">	The dt. </param>
		/// <param name="mt">	The mt. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SetColumnsMapping( DataTable dt, MappingType mt ){
				foreach( DataColumn dc in dt.Columns )
					dc.ColumnMapping = mt;
		}
		#endregion Levels ============

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets an outer xml. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="xmlString">	The xml string. </param>
		///
		/// <returns>	The outer xml. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetOuterXML(string xmlString){return GetInnerXML("<A>"+xmlString+"</A>");}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets an inner xml. </summary>
		///
		/// <remarks>	Dimon, 11/23/2010. </remarks>
		///
		/// <param name="xmlString">	The xml string. </param>
		///
		/// <returns>	The inner xml. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetInnerXML(string xmlString){
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(xmlString);
			StringWriter xsw = new StringWriter();
			XmlTextWriter xtw = new XmlTextWriter(xsw);
			xtw.Formatting = Formatting.Indented;
			doc.DocumentElement.WriteContentTo(xtw);
			return xsw.ToString();
		}
	}
}
